#!/usr/bin/python2.7
# -*- coding=UTF-8 -*-

import re, os
import sys
import getopt

# 先处理成无注释注解模式的文本
def remove_comments(file):
    '''
    将原始的MySQL脚本处理成没有注释和注解的简单脚本

    Args:
        file: SQL文件的路径
    Returns: 
        返回一个元组：(ddl语句，索引，主键，表名)
    '''
    sql = ''
    index = ''
    pk = ''
    table_name = ''
    for line in file:
        if not (re.match('--.*?$', line, flags=re.I) or re.match(r'/\*.*?;$', line, flags=re.I)):
            if re.search(r'CREATE TABLE',  line, flags=re.I):
                table_name = line.replace(r'CREATE TABLE ', '').replace('`', '').replace(r'(', '')
            elif not (re.search(r'\sPRIMARY', line, flags=re.I) 
            or re.search(r'\sUNIQUE', line, flags=re.I) 
            or re.search(r'\sKEY', line, flags=re.I) 
            or re.search(r'DROP.*?;$', line, flags=re.I)):
                line = re.sub(r'\sCOMMENT .*?,$', '', line).replace('`', '')
                line = re.sub(r'\)\sENGINE.*?;$', '', line)
                line = re.sub(r'CREATE TABLE.*?$', '', line)
                line = re.sub(r'SYS_.*?$', '', line) # 临时删除统一字段
                line = re.sub(r'^(\s*)', '', line)
                sql += line
            elif re.search(r'\sPRIMARY', line, flags=re.I):
                pk = line.replace(',', '').replace('`', '')
            elif re.search(r'\sUNIQUE', line, flags=re.I) or re.search(r'\sKEY', line, flags=re.I):
                index += line.replace(',', '').replace('`', '').lstrip()
    return (sql, index, pk, table_name)

def convert_ddl_to_tuple(ddl):
    '''
    将ddl语句处理成元组：(列名，列类型，约束+默认值)

    Args:
        ddl MySQl的列定义语句
    Returns: 
        返回规整好的元组：(列名，列类型，约束+默认值)
    '''
    ddl_str = ddl.split('\n')[:-1]
    meta_list = []
    for ddl in ddl_str:
        element = ddl.split(' ')
        col = element[0]
        typ = element[1]
        constraints = element[2:]
        # 不能出现null default 开头
        if len(constraints) > 0 and constraints[0] == 'NULL':
            del constraints[0]
        cons = ''
        for constraint in constraints:
            cons += constraint
            cons += ' '
        meta = (col, typ, cons)
        meta_list.append(meta)
    return meta_list

#处理ddl语句，简单实现，用硬编码形式
def convert_ddl_to_mysql(meta_list):
    '''
        处理所有的列定义语句，主要处理列类型的转换

        Args:
            meta_list convert_ddl_to_tuple函数返回的元组
        Returns：
            返回符合Oracle标准的列定义元组：(列名，列类型，约束+默认值)
    '''
    oracle_meta_list = []
    for meta in meta_list:
        if re.search(r'^int.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'number(10)', meta[2]))
        elif re.search(r'^bigint.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'number(20)', meta[2]))
        elif re.search(r'^varchar.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], convert_varchar(meta[1]), meta[2]))
        elif re.search(r'^tinyint.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'number(3)', meta[2]))
        elif re.search(r'text.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'clob', meta[2]))
        elif re.search(r'blob.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'blob', meta[2]))
        elif re.search(r'datetime.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'date', meta[2]))
        elif re.search(r'timestamp.*?$', meta[1], flags=re.I):
            oracle_meta_list.append((meta[0], r'date', meta[2]))
    return oracle_meta_list

def convert_constraint_to_oracle(oracle_meta_list):
    '''
    将约束转换成Oracle的形式

    Args:
        oracle_meta_list: 已经转换成Oracle列定义方式的元组列表
    Returns:
        返回处理成Oracle形式的列定义语句
    '''
    meta_list = []
    for meta in oracle_meta_list:
        if re.search('NOT NULL', meta[2], flags=re.I) and re.search('DEFAULT', meta[2], flags=re.I):
            temp_list = meta[2].rstrip().split(' ')
            tup = (meta[0], meta[1], temp_list[2] + ' ' + temp_list[3] + ' ' + temp_list[0] + ' ' +temp_list[1])
            meta_list.append(tup)
        else:
            meta_list.append(meta)
    return meta_list

def general_ddl(table_name, meta_list, pk, index_ddl_list, dest_dir):
    '''
        生成ddl语句，会将转换好的语句写入到目标路径的文件中

        Args:
            table_name: 表名
            meta_list: 列定义sql语句列表
            pk: 主键定义
            index_ddl_list: 索引定义语句
            dest_dir: 目标sql文件路径
    '''
    sql_str = 'CREATE TABLE ' + table_name + '(\n'
    for meta in meta_list:
        sql_str += meta[0] + ' ' + meta[1] + ' ' + meta[2] + ',\n'
    sql_str += 'sys_ctime date default sysdate not null,\n'
    sql_str += 'sys_utime date default sysdate not null,\n'
    sql_str += 'sys_valid number(3) default 1 not null,\n'
    sql_str += pk + ');'
    for index_ddl in index_ddl_list:
        sql_str += index_ddl
    with open(dest_dir + table_name.replace('\n', '') + '.sql', 'w') as w:
        w.write(sql_str)

def convert_index_to_oracle(index_ddl, table_name):
    '''
        将索引语句转换成Oracle形式

        Args:
            index_ddl: 索引定义语句
            table_name: 表名
        Returns:
            转换成Oracle形式的索引定义语句，列表形式，每个元素是一个定义语句
    '''
    index_list = index_ddl.split('\n')
    index_ddl_list = []
    # print index_list 
    for index in index_list:
        if re.search(r'UNIQUE.*?$', index, flags=re.I):
            index_name = index.split(' ')[2]
            index_col = index.split(' ')[3]
            unique_ddl = 'ALTER TABLE ' + table_name + ' ADD CONSTRAINT ' + index_name + ' UNIQUE ' + index_col + ';\n'
            index_ddl_list.append(unique_ddl)
        elif re.search(r'^KEY.*?$', index, flags=re.I):
            print index.split(' ')
            index_name = index.split(' ')[1]
            index_col = index.split(' ')[2]
            unique_ddl = 'CREATE INDEX ' + index_name + ' ON ' + table_name.replace('\n', '') + index_col + ';\n'
            index_ddl_list.append(unique_ddl)
    return index_ddl_list

def convert_varchar(varchar):
    return varchar.replace('varchar', 'varchar2').replace(')', ' char)')

def handler(file, dest_dir):
    f = open(file, 'r')
    origin_ddl = remove_comments(f)
    index_ddl = convert_index_to_oracle(origin_ddl[1], origin_ddl[3])
    meta_list = convert_ddl_to_tuple(origin_ddl[0])
    oracle_list = convert_ddl_to_mysql(meta_list)
    final_list = convert_constraint_to_oracle(oracle_list)
    general_ddl(origin_ddl[3], final_list, origin_ddl[2], index_ddl, dest_dir)

def parse_args():
    values = {'source_dir': '', 'dest_dir': ''}
    sysArgv = sys.argv[1:]
    try:
        opts, _ = getopt.getopt(sysArgv, 'S:D:')
    except getopt.GetoptError:
        return False
    
    for opt, arg in opts:
        if opt in ('-S'):
            values['source_dir'] = arg
        elif opt in ('-D'):
            values['dest_dir'] = arg
    if (values['source_dir'] == "") or (values['dest_dir'] == ""):
        return False
    return values

def main():
    cmd_values = parse_args()
    if not cmd_values:
        exit(-1)
    dest_dir = cmd_values['dest_dir']
    for root, _, files in os.walk(cmd_values['source_dir']):
        for file in files:
            handler(os.path.join(root, file), dest_dir)

if __name__=='__main__':
    main()